package database

import (
	"database/sql"
	_ "embed"
	"errors"
	"fmt"
	"gitee.com/zaper/private-logger"
	"gitee.com/zaper/service-center-client/util"
	"strconv"
	"strings"
	"sync"
	"time"
)

const (
	semicolon = ";"
)

var (
	properties     *util.Properties
	dbManager      *DBManager
	configFileLock sync.RWMutex // 保护配置相关资源
	dbManagerLock  sync.RWMutex // 保护数据相关资源
	dbExecLocal    sync.RWMutex
)

type DBManager struct {
	db *sql.DB
}

func GetDataBaseManager() *DBManager {
	dbManagerLock.Lock()
	defer dbManagerLock.Unlock()
	if dbManager == nil {
		dbConn, err := connectDB()
		if err != nil {
			logger.Errorf("connect db is fail %s", err)
			return nil
		}
		dbManager = &DBManager{
			db: dbConn,
		}
	}
	return dbManager
}

func connectDB() (*sql.DB, error) {
	// 获取基础参数
	p := GetDBConfigInfo()
	user := p.Data["user"]
	passwd := p.Data["passwd"]
	host := p.Data["host"]
	port := p.Data["port"]
	dbName := p.Data["dbName"]
	dsn := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?parseTime=true", user, passwd, host, port, dbName)
	logger.Printf("mysql connect url is %s", dsn)

	// 连接数据库
	db, err := sql.Open("mysql", dsn)
	if err != nil {
		logger.Errorf("open db connect fail %s", err)
		return nil, errors.New("open db connect fail")
	}

	// 高级配置参数
	maxOpenConn, _ := strconv.ParseInt(p.Data["MaxOpenConns"], 10, 64)
	maxIdleConn, _ := strconv.ParseInt(p.Data["MaxIdleConns"], 10, 64)
	db.SetMaxOpenConns(int(maxOpenConn))
	db.SetMaxIdleConns(int(maxIdleConn))

	// 尝试ping数据库
	if err := db.Ping(); err != nil {
		return nil, errors.New("db ping fail")
	}
	// 异步监听db状态
	go monitorDbState(db)
	return db, nil
}

func (dbManager *DBManager) CreateDB(dbName string) error {
	if dbManager.db == nil {
		logger.Error("input db is nil")
		return errors.New("input db is nil")
	}
	// 库不存在时创表
	sqlStr := fmt.Sprintf("CREATE DATABASE IF NOT EXISTS %s CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci", dbName)
	_, err := dbManager.db.Exec(sqlStr)
	if err != nil {
		logger.Error("create database %s fail", dbName)
		return err
	}
	return nil
}

// BatchExecSql 一般给创建数据库，创建表使用，不存在sql注入，可以直接使用静态语句
func (dbManager *DBManager) BatchExecSql(sqlBatch []string) error {
	dbExecLocal.Lock()
	defer dbExecLocal.Unlock()
	if sqlBatch == nil {
		logger.Info("No sql need initialize")
		return nil
	}
	for _, sqlState := range sqlBatch {
		_, err := dbManager.db.Exec(sqlState)
		if err != nil {
			logger.Errorf("exec sql file is fail, err is %s", err)
			return errors.New("exec sql file is fail")
		}
	}
	logger.Infof("All sql initliaze success !")
	return nil
}

func (dbManager *DBManager) PrepareExecSql(sqlParam string, args ...any) error {
	dbExecLocal.Lock()
	defer dbExecLocal.Unlock()
	logger.Infof("PreExec sql is %s", sqlParam)
	if sqlParam == "" {
		return errors.New("input sql fail, is nil")
	}

	// 无意义的事务，仅供了解功能使用
	begin, _ := dbManager.db.Begin()
	stmt, err := begin.Prepare(sqlParam)
	defer func() {
		err := stmt.Close()
		if err != nil {
			logger.Errorf("Close sqlstmt fail, error is %s", err)
			return
		}
	}()
	if err != nil {
		err := begin.Rollback()
		return err
	}
	_, err = stmt.Exec(args...)
	if err != nil {
		_ = begin.Rollback()
		return errors.New("exec sql fail")
	}
	_ = begin.Commit()
	logger.Infof("Sql prepareExec success !")
	return nil
}

//scanFieil 示例
//
//func(rows *sql.Rows) error {
//	defer func() {
//		err = rows.Close()
//		if err != nil {
//			logger.Infof("rows close fail , err is %s", err)
//		}
//	}()
//	for rows.Next() {
//		model := &hot_patch_client_service.HotPatchClientModel{}
//		err = rows.Scan(&model.ProcessName, &model.ProcessIp, &model.ProcessPort, &model.ProcessType, &model.DateTime)
//		if err != nil {
//			return err
//		}
//		clientModeArr = append(clientModeArr, model)
//	}
//	return nil
//}

func (dbManager *DBManager) QueryRowSql(sqlInfo string, scanField func(rows *sql.Rows) error, args ...any) error {
	var stmt *sql.Stmt
	var row *sql.Rows
	defer func() {
		if row != nil {
			closeErr := row.Close()
			if closeErr != nil {
				logger.Errorf("Close row is fail, err is %s", closeErr)
			}
		}
		if stmt != nil {
			closeErr := stmt.Close()
			if closeErr != nil {
				logger.Errorf("Close stmt is fail, err is %s", closeErr)
			}
		}
	}()
	if sqlInfo == "" {
		logger.Info("No sql need initialize")
		return nil
	}
	stmt, err := dbManager.db.Prepare(sqlInfo)
	if err != nil {
		return err
	}
	row, err = stmt.Query(args...)
	if err != nil {
		return err
	}
	err = scanField(row)
	if err != nil {
		logger.Infof("Query database fail, err is %s", err)
		return err
	}
	return nil
}

func (dbManager *DBManager) QueryCountSql(sqlInfo string, args ...any) (int, error) {
	var stmt *sql.Stmt
	var row *sql.Rows
	defer func() {
		if row != nil {
			closeErr := row.Close()
			if closeErr != nil {
				logger.Errorf("Close row is fail, err is %s", closeErr)
			}
		}
		if stmt != nil {
			closeErr := stmt.Close()
			if closeErr != nil {
				logger.Errorf("Close stmt is fail, err is %s", closeErr)
			}
		}
	}()
	if sqlInfo == "" {
		logger.Info("No sql need initialize")
		return -1, nil
	}
	logger.Infof("PreExec sql is %s", sqlInfo)
	stmt, err := dbManager.db.Prepare(sqlInfo)
	if err != nil {
		logger.Infof("Prepare sql fail, err is %s", err)
		return -1, err
	}
	row, err = stmt.Query(args...)
	if err != nil {
		logger.Infof("Query database fail, err is %s", err)
		return -1, err
	}
	row.Next()
	var count int
	err = row.Scan(&count)
	if err != nil {
		logger.Infof("Scan row fail, err is %s", err)
		return -1, err
	}
	return count, nil
}

//go:embed config/db_connect.properties
var dbConfig string

func GetDBConfigInfo() *util.Properties {
	configFileLock.Lock()
	defer configFileLock.Unlock()
	if properties == nil {
		pies, err := util.ParseStringToProperties(dbConfig)
		if err != nil {
			logger.Info("read db_connect file fail")
			return nil
		}
		properties = pies
	}
	return properties
}

func (dbManager *DBManager) SplitSqlOperations(sqlOperations string) []string {
	if sqlOperations == "" {
		logger.Error("input sql operation is empty")
		return nil
	}
	sqlArr := strings.Split(sqlOperations, ";")
	var sqlStateArr []string
	for _, sqlTemp := range sqlArr {
		var sqlState string
		// 空行直接跳过不计算后续分割
		if sqlTemp == "" {
			continue
		}
		subSqlArr := strings.Split(sqlTemp, "\n")
		for _, s := range subSqlArr {
			// 前后去空
			s = strings.TrimSpace(s)
			if s == "" {
				continue
			}
			// 如果某行包含注解符号"--"，即注解符之后必然都是注解，则从头截取到注解符之前
			// 截取后并去掉前后的空格，保证sql语句的紧凑性
			if strings.Contains(s, "--") {
				s = strings.TrimSpace(s[:strings.Index(s, "--")])
			}
			sqlState += s
		}
		// 处理好的sql语句追加到sqlStateArr
		sqlStateArr = append(sqlStateArr, sqlState)
	}
	logger.Infof("Src sql operations %s to tar sql operations is %s", sqlOperations, sqlStateArr)
	return sqlStateArr
}

func monitorDbState(db *sql.DB) {
	ticker := time.NewTicker(2 * time.Second)
	defer ticker.Stop()
	for {
		select {
		case <-ticker.C:
			stats := db.Stats()
			logger.Infof("DB openConnections: %d, InUse: %d, Idle: %d",
				stats.OpenConnections, stats.InUse, stats.Idle)
		}
	}
}
